import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from scipy import stats

df=pd.read_excel('result1.xlsx',sheet_name='国家分类季度同比增长率')
df['quarter']=df['quarter'].apply(lambda x:(3*x)/12)
df['year_quarter']=df['year']+df['quarter']
area=df['国家'].unique()
df2=pd.DataFrame(columns=('国家','销售额预测值','利润预测值'))



for i in area:
    data=df[df['国家']==i]
    x=data['year_quarter']
    y1=data['销售额']
    y2=data['利润']
    s, intercept, r, p, std_err = stats.linregress(x, y1) 
    def func1(x):
        return s * x + intercept
    s1, intercept1, r1, p1, std_err1 = stats.linregress(x, y2) 
    def func2(x):
        return s1* x + intercept1

    area=i
    forecast1=func1(2021.25)
    forecast2=func2(2021.25)
    df2=df2.append(pd.DataFrame({'国家':[area],'销售额预测值':[forecast1],'利润预测值':[forecast2]}))
df2.to_excel('国家预测.xlsx',index=False)
